import threading
from multiprocessing import Process, Queue
from threading import Thread
import gevent
from utils.mysqlUtils.db_utils import DB
from utils.mysqlUtils.sql_server_utils import MSSQL
from utils.readFileUtils.excel_control import write_excle_data
from utils.logging_tool.log_control import ERROR, INFO, WARNING
from utils.noticeUtils.dingtalkControl import DingTalkSendMsg
import decimal
import os, time


class Testishotdata:
    """
    Listing 迁移
    """

    # def redis_db(self):

    # ERP生产库
    def erp_db(self, sql, value):

        erp_base = MSSQL("sqlserver_db_erp_base")
        erp_res = erp_base.ExecQuery(sql=sql, value=value)
        return erp_res

    # 冷数据库
    def cold_db(self, sql):

        cold_base = DB("mysql_db_code_base")
        cold_res = cold_base.execute_sql_many(sql)
        return cold_res

    # 数据比对
    def data_comparison(self, erp_sql, cold_sql):

        erp_data = self.erp_db(erp_sql)
        cold_data = self.cold_db(cold_sql)

        start_time = time.time()

        error_list = []
        for i in cold_data:
            if i not in erp_data:
                error_list.append(i)

        print('%s cost %d second' % (threading.current_thread().getName(), time.time() - start_time))

        # test_report = f'检测数据合计{len(erp_data)}条数据, 存在异常数据{len(error_list)}条'
        # dingtalk_report = f'''检测数据合计{len(erp_data)}条 \n
        #        存在异常数据{len(error_list)}条 \n
        #        异常数据明细: \n{error_list} \n
        #        '''
        # DingTalkSendMsg().send_migration_report(dingtalk_report)

    # sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM RB_Amazon_Product_Base(nolock) WHERE CustomerID = 1 AND OrderSourceID in (5059,5005,5086)  AND OrderSourceSKUID > 0 AND AddTime < '2022-11-14';"
    # ERP生产库 - 冷数据库
    def base_comparison(self, cold_sql):

        # 获取erp库数据
        # erp_data = self.erp_db(erp_sql)
        # mysql_sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep86 WHERE OrderSourceID in (5059,5005,5086);"
        cold_data = self.cold_db(cold_sql)

        # sum_data = len(erp_data)

        # 创建队列
        q = Queue()
        # 创建接收异常数据队列
        eq = Queue()

        # 将数据写入队列
        if len(cold_data) != 0:
            for x in cold_data:
                # x['ListingPrice'] = x['ListingPrice'].quantize(decimal.Decimal('0.000000'))
                # x['ListingPrice'] = float(x['ListingPrice'])
                q.put(x)
        sum_data = q.qsize()
        print("推入队列{}".format(q.qsize()))
        pro_list = []

        # 开启2个进程处理
        for i in range(5):
            pname = 'pro{}'.format(i)
            p = Process(target=self.process_work, args=(q, eq, pname))
            p.start()
            pro_list.append(p)
        # 让主进程等待子进程执行结束在往下执行
        for p in pro_list:
            p.join()
        # 获取队列中的异常数据添加至列表

        error_data = []

        while not eq.empty():
            error_list = eq.get(timeout=0.01)
            error_data.append(error_list)
        sum_error = len(error_data)

        # 将异常数据写入文档
        write_excle_data(error_data, filedir=r"D:\zhcx-service-test\Common")
        # 输出报告
        test_report = f'检测数据合计{sum_data}条数据, 存在异常数据{sum_error}条' \
                      f'存在异常数据{sum_error}条 \n' \
                      f'异常数据明细: \n{error_data} \n'
        # dingtalk_report = f'''检测数据合计{sum_data}条 \n
        # 存在异常数据{sum_error}条 \n
        # 异常数据明细: \n{error_data} \n

        # DingTalkSendMsg().send_migration_report(dingtalk_report)
        print(test_report)
        return test_report

    def green_work(self, q, eq, gname):

        sql = "SELECT * FROM RB_Amazon_Product_Base(nolock) WHERE CustomerID = 1 AND OrderSourceSKU=%s AND ASINorItemID=%s AND SKU=%s"
        count = 0
        while not q.empty():
            try:
                except_json = q.get(timeout=0.01)
                except_json = (except_json['OrderSourceSKU'], except_json['ASINorItemID'], except_json['SKU'])
                erp_data = self.erp_db(sql=sql, value=except_json)
                # print('------协程{}执行了{}个任务'.format(gname,count))
                if len(erp_data) == 0:
                    eq.put(except_json)
                # for i in erp_data:
                #
                # # except_json['ListingPrice'] = float(except_json['ListingPrice'])
                #     if except_json != i :
                #
                print()
                #         eq.put(except_json)
                #     else:
                #         pass
            except Exception as e:
                print(e)
                continue
            gevent.sleep(0.01)
            count += 1
        print('--------------协程{}执行了{}个任务'.format(gname, count))

    def thread_work(self, q, eq, tname):

        g_list = []
        for i in range(5):
            gname = '{}-g-{}'.format(tname, i)
            # print('创建协程------{}'.format(gname))
            g = gevent.spawn(self.green_work, q, eq, gname)
            g_list.append(g)
        gevent.joinall(g_list)

    def process_work(self, q, eq, pname):

        # 创建2个线程，并执行
        thread_list = []
        for i in range(2):
            tname = '{}-th-{}'.format(pname, i)
            # print('创建线程{}'.format(tname))
            t = Thread(target=self.thread_work, args=(q, eq, tname))
            thread_list.append(t)
            # t.setDaemon(True)
            t.start()
        # 让主线程堵塞，等待子线程执行完后在执行
        for t in thread_list:
            t.join()


if __name__ == '__main__':
    sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM RB_Amazon_Product_Base(nolock) WHERE CustomerID = 1 AND OrderSourceID in (5059,5005,5086)  AND OrderSourceSKUID > 0 AND AddTime < '2022-11-14';"
    mysql_sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep86 WHERE OrderSourceID in (5059);"
    # sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM RB_Amazon_Product_Base WHERE CustomerID = 1 AND OrderSourceID = 5406 AND OrderSourceSKUID > 0 AND AddTime < '2022-11-14';"
    # mysql_sql = "SELECT OrderSourceSKU, ASINorItemID, SKU FROM aml_amazon_product_base_dep86 WHERE OrderSourceID = 5406;"
    Testishotdata().base_comparison(cold_sql=mysql_sql)
